# -*- coding: utf-8 -*-
class Product < ActiveRecord::Base
  belongs_to :company
  belongs_to :vendor
  belongs_to :brand
  belongs_to :product_type

  has_many :sale_order_details
  has_many :product_collections
  has_many :purchase_order_details
  has_many :opening_stock_details
  has_many :warehousing_entry_details

  has_many :prices
  has_many :products_components,:dependent => :destroy
  
  #得到产品所对应的每一个组件的集合
  has_many :components ,:class_name=>"Product" ,:through=>:products_components ,:foreign_key=>"component_id"
  
  has_many :products ,:class_name=>"Product" ,:through=>:products_components ,:foreign_key=>"product_id"
  belongs_to :products_component ,:class_name=>"ProductsComponent" ,:foreign_key=>"product_id"

  #scope :name_like, lambda { |name| { :conditions => ['name like ? OR number like ?', '%'+name+'%','%'+name+'%'],:order=>"name"}}

  #用来得到是，或不是套组的所有产品
  scope :with_suit, lambda { |isSuit| where("is_suit = ?", isSuit) }

  #是套组的产品
  scope :is_suit,with_suit(1)
  #不是套组的产品
  scope :is_not_suit,with_suit(0)

  scope :name_like, lambda {  |name|
    where('name like ? OR number like ?', '%'+name+'%','%'+name+'%').order("name")
  }

  #专为采购设置的，抛弃所有的套组的产品
  scope :name_like_for_purchase, lambda {  |name|
    where('name like ? OR number like ?', '%'+name+'%','%'+name+'%').order("name").merge(Product.is_not_suit)
  }

  scope :products_of_saleOrder, {
    :select => "episodes.*",
    :joins => "INNER JOIN series ON series.id = clips.owner_id INNER JOIN shows on shows.id = series.show_id",
    :conditions=>"shows.visible = 1 AND clips.owner_type = 'Series' "
  }

  
  scope :paginate, lambda { |start,limit| { :offset => start.to_i,:limit => limit.to_i}}

  #计算成本均价
  def self.cost_price endDate, calYear, calMonth , companyId , employeeId
    logger.info endDate
    logger.info calYear
    logger.info calMonth
    #这儿要作一些补充，即只有审批通过的新增，修改才可以计算成本。有申请状态的，不计算在内。
    #所以要首先判断，在时间段内，有没有牌申请状态的采购，如果有就暂时不进行成本计算。
    #同时要把计算同期内的产品的product_id提交导入进去。
    #首先删除所有符合条件的id.年，月,暂时屏蔽掉
    #CostPrice.delete_all("cal_year = #{calYear} AND cal_month = #{calMonth} AND company_id = #{session[:company_id]}")
    #然后把空的产品的ID和年，月，公司ID，插入cost_prices表中

    #select id from products where id not in (select product_id from cost_prices)
    #用这一句，来看一下有没有产品的变化，更快。如果没有，就插入。速度提高几千倍。
    products = Product.where("company_id = #{companyId} AND id not in (select product_id from cost_prices where cal_year = #{calYear} AND cal_month = #{calMonth} )")
    if products.length > 0
      products.each do |product|
        costPrice = CostPrice.new
        costPrice.product_id = product.id
        costPrice.employee_id = employeeId
        costPrice.company_id = companyId
        costPrice.cal_year = calYear.to_i
        costPrice.cal_month = calMonth.to_i
        costPrice.save!
      end
    end
    
    begin
      #这里的计算是很重要的，如果单纯以采购来计算，那么并不合适，应该以已经入库的所有的订单为准。这样的加权才是对的。
      #因为有些采购订单并没有完全入库，这样的订单不适合作成本
      #这是计算当前最新含税成本，也要保留，以方便库存的计算。
      ActiveRecord::Base.connection.execute("update products p inner join (select product_id,sum(amount)/sum(quantity) cost_price,ROUND((sum(tax_amount)/sum(quantity)),2) tax_price
                                    from (select wed.product_id, wed.quantity,pod.amount,pod.tax_amount,po.tax_rate
                                          from warehousing_entries we,warehousing_entry_details wed,purchase_orders po,purchase_order_details pod
                                          where we.auditing_status not like '%申请%'
                                                AND we.auditing_status not like '%撤消%'
                                                AND we.auditing_status != ''
                                                AND po.auditing_status not like '%申请%'
                                                AND po.auditing_status not like '%撤消%'
                                                AND po.auditing_status != ''
                                                AND wed.purchase_order_id = po.id
                                                AND wed.warehousing_entry_id = we.id
                                                AND pod.purchase_order_id = po.id
                                                AND pod.product_id = wed.product_id
                                                AND po.company_id = #{companyId}
                                          union all
                                          select osd.product_id, osd.quantity,osd.amount,osd.tax_amount,os.tax_rate from opening_stock_details osd,opening_stocks os
                                          where osd.opening_stock_id = os.id
                                                AND os.auditing_status not like '%申请%'
                                                AND os.auditing_status not like '%撤消%'
                                                AND os.auditing_status != ''
                                                AND os.company_id = #{companyId}
                                           ) as t group by product_id) tp on tp.product_id = p.id AND p.company_id = #{companyId}
                                         set p.cost_price = tp.cost_price,p.tax_price = tp.tax_price")
      #这是根据年，月，计算相关年月的含税成本价。这儿应该是实际入库为日期判断条件，以入库量计算成本
      ActiveRecord::Base.connection.execute("update cost_prices cp inner join (select product_id,sum(amount)/sum(quantity) cost_price,ROUND((sum(tax_amount)/sum(quantity)),2) tax_price
                                    from (select wed.product_id, wed.quantity,pod.amount,pod.tax_amount,po.tax_rate
                                          from warehousing_entries we,warehousing_entry_details wed,purchase_orders po,purchase_order_details pod
                                          where we.action_date < '#{endDate}'
                                                AND we.auditing_status not like '%申请%'
                                                AND we.auditing_status not like '%撤消%'
                                                AND we.auditing_status != ''
                                                AND po.auditing_status not like '%申请%'
                                                AND po.auditing_status not like '%撤消%'
                                                AND po.auditing_status != ''
                                                AND wed.purchase_order_id = po.id
                                                AND wed.warehousing_entry_id = we.id
                                                AND pod.purchase_order_id = po.id
                                                AND pod.product_id = wed.product_id
                                                AND po.company_id = #{companyId}
                                          union all
                                          select osd.product_id, osd.quantity,osd.amount,osd.tax_amount,os.tax_rate
                                          from opening_stock_details osd,opening_stocks os
                                          where osd.opening_stock_id = os.id 
                                                AND os.auditing_status not like '%申请%'
                                                AND os.auditing_status not like '%撤消%'
                                                AND os.auditing_status != ''
                                                AND os.company_id = #{companyId}
                                           ) as t group by product_id) tp on tp.product_id = cp.product_id AND cp.company_id = #{companyId} AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth}
                                          set cp.cost_price = tp.cost_price,cp.tax_price = tp.tax_price")

      #取后要计算如果是套组的，这种套组的成本问题。
      #给套组价清0，以计算套组的成本及含税。这个是为了防止有些采购过程中有套数，所以这是一个老旧系统兼容的补丁操作。
      ActiveRecord::Base.connection.execute("update cost_prices cp set cp.cost_price = null,cp.tax_price = null
where cp.product_id in (select distinct p1.id from products p1,products_components pc1 where p1.id = pc1.product_id)
      AND cp.company_id = #{companyId} AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth}
        ")

      #在套组清0以后，过行套组的成本计算
      suit_cost_price(calYear, calMonth , companyId , employeeId ,0)

    rescue Exception => e
      logger.info e
      DbLog.info(companyId, 1, employeeId, 'admin', 'cost_price', 'do not know' ,e.to_s)
    end
  end

  #计算含税成本均价
  def self.tax_price endDate, calYear, calMonth , companyId , employeeId
    logger.info endDate
    logger.info calYear
    logger.info calMonth
    #这儿要作一些补充，即只有审批通过的新增，修改才可以计算成本。有申请状态的，不计算在内。
    #所以要首先判断，在时间段内，有没有牌申请状态的采购，如果有就暂时不进行成本计算。
    #同时要把计算同期内的产品的product_id提交导入进去。
    #首先删除所有符合条件的id.年，月,暂时屏蔽掉
    #CostPrice.delete_all("cal_year = #{calYear} AND cal_month = #{calMonth} AND company_id = #{session[:company_id]}")
    #然后把空的产品的ID和年，月，公司ID，插入tax_prices表中
    products = Product.where("company_id = #{companyId} AND id not in (select product_id from cost_prices where cal_year = #{calYear} AND cal_month = #{calMonth} )")
    if products.length > 0
      products.each do |product|
        costPrice = CostPrice.new
        costPrice.product_id = product.id
        costPrice.employee_id = employeeId
        costPrice.company_id = companyId
        costPrice.cal_year = calYear.to_i
        costPrice.cal_month = calMonth.to_i
        costPrice.save!
      end
    end

    begin
      #这是计算当前最新成本，也要保留，以方便库存的计算。
      ActiveRecord::Base.connection.execute("update products p inner join (select product_id,ROUND(avg(tax_price),2) tax_price
                                    from (select pd.product_id, pd.quantity,pd.amount,(pd.amount/pd.quantity)*(1+po.tax_rate*0.01) tax_price from purchase_order_details pd,purchase_orders po
                                          where po.auditing_status not like '%申请%' AND po.auditing_status not like '%撤消%' AND pd.purchase_order_id = po.id AND po.company_id = #{companyId}
                                          union all
                                          select osd.product_id, osd.quantity,osd.amount,(osd.amount/osd.quantity)*(1+os.tax_rate*0.01) tax_price from opening_stock_details osd,opening_stocks os
                                          where osd.opening_stock_id = os.id AND os.auditing_status not like '%申请%' AND os.auditing_status not like '%撤消%' AND os.company_id = #{companyId}
                                           ) as t group by product_id) tp on tp.product_id = p.id AND p.company_id = #{companyId} set p.tax_price = tp.tax_price")
      
      #ActiveRecord::Base.connection.execute("update products p set tax_pirce = ROUND(cost_price*1.17,2) where company_id = #{session[:company_id]}")
      #这是根据年，月，计算相关年月的成本价。
      ActiveRecord::Base.connection.execute("update cost_prices cp inner join (select product_id,ROUND(avg(tax_price),2) tax_price
                                    from (select pd.product_id, pd.quantity,pd.amount,(pd.amount/pd.quantity)*(1+po.tax_rate*0.01) tax_price from purchase_order_details pd,purchase_orders po
                                          where pd.purchase_order_id = po.id AND po.updated_at < '#{endDate}' AND po.auditing_status not like '%申请%' AND po.auditing_status not like '%撤消%' AND po.company_id = #{companyId} 
                                          union all
                                          select osd.product_id, osd.quantity,osd.amount,(osd.amount/osd.quantity)*(1+os.tax_rate*0.01) tax_price from opening_stock_details osd,opening_stocks os
                                          where osd.opening_stock_id = os.id AND os.auditing_status not like '%申请%' AND os.auditing_status not like '%撤消%' AND os.company_id = #{companyId}
                                           ) as t group by product_id) tp on tp.product_id = cp.product_id AND cp.company_id = #{companyId} AND cp.cal_year = #{calYear} AND cp.cal_month = #{calMonth}
                                          set cp.tax_price = tp.tax_price")
      
      
    rescue Exception => e
      logger.info e
      DbLog.info(companyId, 1, employeeId, 'admin', 'cost_price', 'do not know' ,e.to_s)
    end
  end

  #计算某个套组的成本，要求迭代使用，套组中可能还有套组。在使用这个方法之前，要给所有的套数成本价清0
  def self.suit_cost_price calYear, calMonth , companyId , employeeId ,productId
    #=0代理第一次被其它函数调用，不是迭代。
    if productId == 0
      #查出所有的是组件级的成本。
      componentCostPrices = CostPrice.joins(:product).merge(Product.is_suit).where("cost_prices.tax_price is null AND cost_prices.cost_price is null AND cal_year = ? AND cal_month = ? AND cost_prices.company_id = ?",calYear,calMonth,companyId).select("cost_prices.id,cost_prices.product_id,cost_prices.cost_price,cost_prices.tax_price,products.name product_name")
      componentCostPrices.each do |componentCostPrice|
        #logger.info componentCostPrice.product_name
        #logger.info "componentCostPrice.product_id:" + componentCostPrice.product_id.to_s
        #logger.info "componentCostPrice.id:" + componentCostPrice.id.to_s
        #查看这个id下，有没有子产品，循环子产品，遇到套组就迭代然后再加成本值，遇到单品就直接累计成本值
        firstComponents = Product.find(componentCostPrice.product_id).components
        #这个循环是得到一个产品的每一个子组件。
        #logger.info "firstComponents.............fuck............length"
        #logger.info "firstComponents.length:"+firstComponents.length.to_s
        firstComponents.each do |firstComponent|
          #logger.info "fuck.........................................................................."
          #logger.info "firstComponent.name:"+firstComponent.name
          #logger.info firstComponent
          #这一步是判断子组件是不是套数，如果可以循环，说明它是套数
          secondComponents = Product.find(firstComponent.id).components
          #logger.info "secondComponents.length++++++++++++++++++++++++++++++++++++++++++++++++++++"
          #logger.info "firstComponent.id:"+firstComponent.id.to_s
          #logger.info "secondComponents.length:"+secondComponents.length.to_s
          if secondComponents.length > 0
            #这儿如果大于0，说明这本身是个组件，就开始用迭代的方式计算成本了。
            secondComponents.each do |secondComponent|
              Product.suit_cost_price calYear, calMonth , companyId , employeeId , secondComponent.id
            end
          end
          subCostPrice = CostPrice.find(:first,:conditions=>["product_id = ? AND cal_year = ? AND cal_month = ? AND company_id = ?",firstComponent.id,calYear,calMonth,companyId])
          #logger.info "fuck.......................................subCostPrice"
          #logger.info "subCostPrice:"+subCostPrice.tax_price.to_s
          if componentCostPrice.cost_price.nil?
            componentCostPrice.cost_price = 0
          end
          if componentCostPrice.tax_price.nil?
            componentCostPrice.tax_price = 0
          end
          #logger.info "subCostPrice.cost_price========================="
          #logger.info subCostPrice.cost_price.nil?
          #logger.info subCostPrice.cost_price
          #logger.info subCostPrice.tax_price.nil?
          #logger.info subCostPrice.tax_price
          #得到firstComponent.id相对应的产品的数量
          componentQuantity = ProductsComponent.find(:first,:conditions=>["product_id = ? AND component_id = ?",componentCostPrice.product_id,firstComponent.id])
          #logger.info "真的Fuck........................."
          #logger.info "firstComponent.id:"+firstComponent.id.to_s
          #logger.info "subCostPrice.product_id:"+subCostPrice.product_id.to_s
          #logger.info "componentQuantity.quantity:"+componentQuantity.quantity.to_s
          componentCostPrice.cost_price = componentCostPrice.cost_price + subCostPrice.cost_price*componentQuantity.quantity
          componentCostPrice.tax_price = componentCostPrice.tax_price + subCostPrice.tax_price*componentQuantity.quantity
        end        
        componentCostPrice.save!
      end
    else
      #如果有一个ID
      logger.info "这是子成本。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。"
      componentCostPrice = CostPrice.find(:first,:conditions=>["cost_prices.tax_price is null AND cost_prices.cost_price is null AND product_id = ? AND cal_year = ? AND cal_month = ? AND company_id = ?",productId,calYear,calMonth,companyId])
      #logger.info componentCostPrice.product_name
      #logger.info "componentCostPrice.product_id:" + componentCostPrice.product_id.to_s
      #logger.info "componentCostPrice.id:" + componentCostPrice.id.to_s
      #查看这个id下，有没有子产品，循环子产品，遇到套组就迭代然后再加成本值，遇到单品就直接累计成本值
      firstComponents = Product.find(componentCostPrice.product_id).components
      #这个循环是得到一个产品的每一个子组件。
      #logger.info "firstComponents.............fuck............length"
      #logger.info "firstComponents.length:"+firstComponents.length.to_s
      firstComponents.each do |firstComponent|
        #logger.info "fuck.........................................................................."
        #logger.info "firstComponent.name:"+firstComponent.name
        #logger.info firstComponent
        #这一步是判断子组件是不是套数，如果可以循环，说明它是套数
        secondComponents = Product.find(firstComponent.id).components
        #logger.info "secondComponents.length++++++++++++++++++++++++++++++++++++++++++++++++++++"
        #logger.info "firstComponent.id:"+firstComponent.id.to_s
        #logger.info "secondComponents.length:"+secondComponents.length.to_s
        if secondComponents.length > 0
          #这儿如果大于0，说明这本身是个组件，就开始用迭代的方式计算成本了。
          secondComponents.each do |secondComponent|
            Product.suit_cost_price calYear, calMonth , companyId , employeeId , secondComponent.id
          end
        end
        subCostPrice = CostPrice.find(:first,:conditions=>["product_id = ? AND cal_year = ? AND cal_month = ? AND company_id = ?",firstComponent.id,calYear,calMonth,companyId])
        #logger.info "fuck.......................................subCostPrice"
        #logger.info "subCostPrice:"+subCostPrice.tax_price.to_s
        if componentCostPrice.cost_price.nil?
          componentCostPrice.cost_price = 0
        end
        if componentCostPrice.tax_price.nil?
          componentCostPrice.tax_price = 0
        end
        #logger.info "subCostPrice.cost_price========================="
        #logger.info subCostPrice.cost_price.nil?
        #logger.info subCostPrice.cost_price
        #logger.info subCostPrice.tax_price.nil?
        #logger.info subCostPrice.tax_price
        #得到firstComponent.id相对应的产品的数量
        componentQuantity = ProductsComponent.find(:first,:conditions=>["product_id = ? AND component_id = ?",componentCostPrice.product_id,firstComponent.id])
        #logger.info "真的Fuck........................."
        #logger.info "firstComponent.id:"+firstComponent.id.to_s
        #logger.info "subCostPrice.product_id:"+subCostPrice.product_id.to_s
        #logger.info "componentQuantity.quantity:"+componentQuantity.quantity.to_s
        componentCostPrice.cost_price = componentCostPrice.cost_price + subCostPrice.cost_price*componentQuantity.quantity
        componentCostPrice.tax_price = componentCostPrice.tax_price + subCostPrice.tax_price*componentQuantity.quantity
      end
      componentCostPrice.save!
    end
  end
end
